﻿Imports System.Data
Imports System.Data.SqlClient
Imports Sistema_InformacionNaviera.BE
Imports Sistema_InformacionNavier.UTIL

Public Class ViajesDALC

    Public Function ListaViaje(ByVal objViajesBE As ViajesBE) As List(Of ViajesBE)

        Dim sqlListar As String
        Dim objListaViajes As New List(Of ViajesBE)
        Dim objVjeBE As ViajesBE = Nothing
        Dim arrSqlParameter(0) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim blnExito As Boolean
        Dim strMensaje As String = String.Empty
        Dim intI As Integer = 0

        Try

            sqlListar = "up_ConsultarNave"

            arrSqlParameter(0) = New SqlParameter("@DesNave", System.Data.SqlDbType.VarChar)
            arrSqlParameter(0).Value = objViajesBE.DesNave
            objListaViajes = New List(Of ViajesBE)()


            ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, arrSqlParameter)
            dt = ds.Tables(0)

            If dt.Rows.Count > 0 Then

                While intI < dt.Rows.Count
                    objVjeBE = New ViajesBE()
                    objVjeBE.CodNave = dt.Rows(intI).ItemArray(0).ToString
                    objVjeBE.DesNave = dt.Rows(intI).ItemArray(1).ToString
                    intI = intI + 1

                    objListaViajes.Add(objVjeBE)
                End While
            End If
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return objListaViajes
    End Function

    'Public Function ListaNave(ByVal objViajesBE As ViajesBE) As List(Of ViajesBE)

    '    Dim sqlListar As String
    '    Dim objListaViajes As New List(Of ViajesBE)
    '    Dim objVjeBE As ViajesBE = Nothing
    '    Dim arrSqlParameter(0) As SqlParameter
    '    Dim ds As DataSet
    '    Dim dt As DataTable

    '    Dim objDatos As New AccesoDatos()
    '    Dim blnExito As Boolean
    '    Dim strMensaje As String = String.Empty
    '    Dim intI As Integer = 0

    '    Try

    '        sqlListar = "up_ConsultarNave"

    '        arrSqlParameter(0) = New SqlParameter("@DesNave", System.Data.SqlDbType.VarChar)
    '        arrSqlParameter(0).Value = objViajesBE.DesNave
    '        objListaViajes = New List(Of ViajesBE)()


    '        ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, arrSqlParameter)
    '        dt = ds.Tables(0)

    '        If dt.Rows.Count > 0 Then

    '            While intI < dt.Rows.Count
    '                objVjeBE = New ViajesBE()
    '                objVjeBE.CodNave = dt.Rows(intI).ItemArray(0).ToString
    '                objVjeBE.DesNave = dt.Rows(intI).ItemArray(1).ToString
    '                intI = intI + 1

    '                objListaViajes.Add(objVjeBE)
    '            End While
    '        End If
    '    Catch ex As Exception
    '        MsgBox(ex.Message, vbCritical, "Error del sistema")
    '        Return Nothing
    '        Exit Function
    '    End Try

    '    Return objListaViajes
    'End Function

    Public Function ConsultaNaveViaje(ByVal objViajesBE As ViajesBE) As List(Of ViajesBE)

        Dim sqlListar As String
        Dim objListaViajes As New List(Of ViajesBE)
        Dim objVjeBE As ViajesBE = Nothing
        Dim arrSqlParameter(5) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim blnExito As Boolean
        Dim strMensaje As String = String.Empty
        Dim intI As Integer = 0

        Try

            sqlListar = "up_ListaNaveViaje"

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@Lin"
            arrSqlParameter(0).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(0).Value = objViajesBE.Line


            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@Serv"
            arrSqlParameter(1).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(1).Value = objViajesBE.Serv


            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@Desde"
            arrSqlParameter(2).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(2).Value = objViajesBE.Farr


            arrSqlParameter(3) = New SqlParameter()
            arrSqlParameter(3).ParameterName = "@Hasta"
            arrSqlParameter(3).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(3).Value = objViajesBE.Fzar


            arrSqlParameter(4) = New SqlParameter()
            arrSqlParameter(4).ParameterName = "@DesNave"
            arrSqlParameter(4).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(4).Value = objViajesBE.DesNave


            arrSqlParameter(5) = New SqlParameter()
            arrSqlParameter(5).ParameterName = "@KVJE"
            arrSqlParameter(5).SqlDbType = SqlDbType.Int
            arrSqlParameter(5).Value = objViajesBE.Viaje

            ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, arrSqlParameter)
            dt = ds.Tables(0)

            If dt.Rows.Count > 0 Then

                While intI < dt.Rows.Count
                    objVjeBE = New ViajesBE()
                    If objViajesBE.Viaje = 0 Then
                        objVjeBE.Viaje = dt.Rows(intI).ItemArray(0).ToString
                        objVjeBE.CodNave = dt.Rows(intI).ItemArray(1).ToString
                        objVjeBE.DesNave = dt.Rows(intI).ItemArray(2).ToString
                        objVjeBE.Dire = dt.Rows(intI).ItemArray(3).ToString
                        objVjeBE.Nvia = dt.Rows(intI).ItemArray(4).ToString
                        objVjeBE.Line = dt.Rows(intI).ItemArray(5).ToString
                        objVjeBE.Serv = dt.Rows(intI).ItemArray(6).ToString
                        objVjeBE.Farr = dt.Rows(intI).ItemArray(7).ToString
                        objVjeBE.Fzar = dt.Rows(intI).ItemArray(8).ToString
                    Else
                        objVjeBE.Viaje = dt.Rows(intI).ItemArray(0).ToString
                        objVjeBE.CodNave = dt.Rows(intI).ItemArray(1).ToString
                        objVjeBE.DesNave = dt.Rows(intI).ItemArray(2).ToString
                        objVjeBE.Dire = dt.Rows(intI).ItemArray(3).ToString
                        objVjeBE.Nvia = dt.Rows(intI).ItemArray(4).ToString
                        objVjeBE.Farr = dt.Rows(intI).ItemArray(5).ToString
                        objVjeBE.Fzar = dt.Rows(intI).ItemArray(6).ToString
                        objVjeBE.IniDes = dt.Rows(intI).ItemArray(7).ToString
                        objVjeBE.FinDes = dt.Rows(intI).ItemArray(8).ToString
                        objVjeBE.IniEmb = dt.Rows(intI).ItemArray(9).ToString
                        objVjeBE.FinEmb = dt.Rows(intI).ItemArray(10).ToString
                        objVjeBE.Line = dt.Rows(intI).ItemArray(11).ToString
                        objVjeBE.Serv = dt.Rows(intI).ItemArray(12).ToString
                        objVjeBE.ObsImpo = dt.Rows(intI).ItemArray(13).ToString
                        objVjeBE.ObsExpo = dt.Rows(intI).ItemArray(14).ToString
                        objVjeBE.Operador = dt.Rows(intI).ItemArray(15).ToString
                        objVjeBE.Estibador = dt.Rows(intI).ItemArray(16).ToString
                        objVjeBE.Muelle = dt.Rows(intI).ItemArray(17).ToString
                        objVjeBE.AgMaritimo = dt.Rows(intI).ItemArray(18).ToString
                        objVjeBE.TipoCarga = dt.Rows(intI).ItemArray(19).ToString
                        objVjeBE.AgGral = dt.Rows(intI).ItemArray(20).ToString
                        objVjeBE.IniMora = dt.Rows(intI).ItemArray(21).ToString
                        objVjeBE.CodPto = dt.Rows(intI).ItemArray(22).ToString
                        objVjeBE.Puerto = dt.Rows(intI).ItemArray(23).ToString
                    End If
                    intI = intI + 1

                    objListaViajes.Add(objVjeBE)
                End While
            End If
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return objListaViajes
    End Function

    Public Function EliminarNaveViaje(ByVal objNaveViajeBE As ViajesBE, ByRef bExito As Boolean, ByRef sMensaje As String) As Boolean

        Dim arrSqlParameter(0) As SqlParameter

        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@KVJE"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Value = objNaveViajeBE.Viaje

            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("up_NaveViajeEliminar", bExito, sMensaje, arrSqlParameter)

            Return bExito

        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return False
            Exit Function
        End Try

    End Function

    Public Function InsertarViajes(ByVal objViajesBE As ViajesBE, ByRef bExito As Boolean, ByRef sMensaje As String) As Boolean

        Dim arrSqlParameter(21) As SqlParameter

        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@CodNave"
            arrSqlParameter(0).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(0).Value = objViajesBE.CodNave

            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@Dire"
            arrSqlParameter(1).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(1).Value = objViajesBE.Dire

            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@Nvia"
            arrSqlParameter(2).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(2).Value = objViajesBE.Nvia

            arrSqlParameter(3) = New SqlParameter()
            arrSqlParameter(3).ParameterName = "@Puerto"
            arrSqlParameter(3).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(3).Value = objViajesBE.Puerto

            arrSqlParameter(4) = New SqlParameter()
            arrSqlParameter(4).ParameterName = "@Farr"
            arrSqlParameter(4).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(4).Value = objViajesBE.Farr

            arrSqlParameter(5) = New SqlParameter()
            arrSqlParameter(5).ParameterName = "@Fzar"
            arrSqlParameter(5).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(5).Value = objViajesBE.Fzar

            arrSqlParameter(6) = New SqlParameter()
            arrSqlParameter(6).ParameterName = "@IniDes"
            arrSqlParameter(6).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(6).Value = objViajesBE.IniDes

            arrSqlParameter(7) = New SqlParameter()
            arrSqlParameter(7).ParameterName = "@FinDes"
            arrSqlParameter(7).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(7).Value = objViajesBE.FinDes

            arrSqlParameter(8) = New SqlParameter()
            arrSqlParameter(8).ParameterName = "@IniEmb"
            arrSqlParameter(8).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(8).Value = objViajesBE.IniEmb

            arrSqlParameter(9) = New SqlParameter()
            arrSqlParameter(9).ParameterName = "@FinEmb"
            arrSqlParameter(9).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(9).Value = objViajesBE.FinEmb

            arrSqlParameter(10) = New SqlParameter()
            arrSqlParameter(10).ParameterName = "@Line"
            arrSqlParameter(10).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(10).Value = objViajesBE.Line

            arrSqlParameter(11) = New SqlParameter()
            arrSqlParameter(11).ParameterName = "@Serv"
            arrSqlParameter(11).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(11).Value = objViajesBE.Serv

            arrSqlParameter(12) = New SqlParameter()
            arrSqlParameter(12).ParameterName = "@Operador"
            arrSqlParameter(12).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(12).Value = objViajesBE.Operador

            arrSqlParameter(13) = New SqlParameter()
            arrSqlParameter(13).ParameterName = "@Estibador"
            arrSqlParameter(13).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(13).Value = objViajesBE.Estibador

            arrSqlParameter(14) = New SqlParameter()
            arrSqlParameter(14).ParameterName = "@Muelle"
            arrSqlParameter(14).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(14).Value = objViajesBE.Muelle

            arrSqlParameter(15) = New SqlParameter()
            arrSqlParameter(15).ParameterName = "@AgMaritimo"
            arrSqlParameter(15).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(15).Value = objViajesBE.AgMaritimo

            arrSqlParameter(16) = New SqlParameter()
            arrSqlParameter(16).ParameterName = "@TipoCarga"
            arrSqlParameter(16).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(16).Value = objViajesBE.TipoCarga

            arrSqlParameter(17) = New SqlParameter()
            arrSqlParameter(17).ParameterName = "@AgGral"
            arrSqlParameter(17).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(17).Value = objViajesBE.AgGral

            arrSqlParameter(18) = New SqlParameter()
            arrSqlParameter(18).ParameterName = "@IniMora"
            arrSqlParameter(18).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(18).Value = objViajesBE.IniMora

            arrSqlParameter(19) = New SqlParameter()
            arrSqlParameter(19).ParameterName = "@ObsImpo"
            arrSqlParameter(19).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(19).Value = objViajesBE.ObsImpo

            arrSqlParameter(20) = New SqlParameter()
            arrSqlParameter(20).ParameterName = "@ObsExpo"
            arrSqlParameter(20).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(20).Value = objViajesBE.ObsExpo


            arrSqlParameter(21) = New SqlParameter()
            arrSqlParameter(21).ParameterName = "@User"
            arrSqlParameter(21).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(21).Value = objViajesBE.Usuario


            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("up_NaveViaje_Insertar", bExito, sMensaje, arrSqlParameter)

            Return bExito

        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return False
            Exit Function
        End Try

    End Function

    Public Function EditarViajes(ByVal objViajesBE As ViajesBE, ByRef bExito As Boolean, ByRef sMensaje As String) As Boolean

        Dim arrSqlParameter(13) As SqlParameter

        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@Viaje"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Value = objViajesBE.Viaje 

            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@IniDes"
            arrSqlParameter(1).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(1).Value = objViajesBE.IniDes

            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@FinDes"
            arrSqlParameter(2).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(2).Value = objViajesBE.FinDes

            arrSqlParameter(3) = New SqlParameter()
            arrSqlParameter(3).ParameterName = "@IniEmb"
            arrSqlParameter(3).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(3).Value = objViajesBE.IniEmb

            arrSqlParameter(4) = New SqlParameter()
            arrSqlParameter(4).ParameterName = "@FinEmb"
            arrSqlParameter(4).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(4).Value = objViajesBE.FinEmb

            arrSqlParameter(5) = New SqlParameter()
            arrSqlParameter(5).ParameterName = "@Estibador"
            arrSqlParameter(5).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(5).Value = objViajesBE.Estibador

            arrSqlParameter(6) = New SqlParameter()
            arrSqlParameter(6).ParameterName = "@Muelle"
            arrSqlParameter(6).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(6).Value = objViajesBE.Muelle

            arrSqlParameter(7) = New SqlParameter()
            arrSqlParameter(7).ParameterName = "@AgMaritimo"
            arrSqlParameter(7).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(7).Value = objViajesBE.AgMaritimo

            arrSqlParameter(8) = New SqlParameter()
            arrSqlParameter(8).ParameterName = "@TipoCarga"
            arrSqlParameter(8).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(8).Value = objViajesBE.TipoCarga

            arrSqlParameter(9) = New SqlParameter()
            arrSqlParameter(9).ParameterName = "@AgGral"
            arrSqlParameter(9).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(9).Value = objViajesBE.AgGral

            arrSqlParameter(10) = New SqlParameter()
            arrSqlParameter(10).ParameterName = "@IniMora"
            arrSqlParameter(10).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(10).Value = objViajesBE.IniMora

            arrSqlParameter(11) = New SqlParameter()
            arrSqlParameter(11).ParameterName = "@ObsImpo"
            arrSqlParameter(11).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(11).Value = objViajesBE.ObsImpo

            arrSqlParameter(12) = New SqlParameter()
            arrSqlParameter(12).ParameterName = "@ObsExpo"
            arrSqlParameter(12).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(12).Value = objViajesBE.ObsExpo


            arrSqlParameter(13) = New SqlParameter()
            arrSqlParameter(13).ParameterName = "@User"
            arrSqlParameter(13).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(13).Value = objViajesBE.Usuario

            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("up_NaveViaje_Editar", bExito, sMensaje, arrSqlParameter)

            Return bExito

        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return False
            Exit Function
        End Try

    End Function

    Public Function ListaNaveViaje(ByVal objViajesBE As ViajesBE) As List(Of ViajesBE)

        Dim sqlListar As String
        Dim objListaViajes As New List(Of ViajesBE)
        Dim objVjeBE As ViajesBE = Nothing
        Dim arrSqlParameter(4) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim blnExito As Boolean
        Dim strMensaje As String = String.Empty
        Dim intI As Integer = 0

        Try

            sqlListar = "up_ListaNaveViaje"

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@Lin"
            arrSqlParameter(0).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(0).Value = objViajesBE.Line


            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@Serv"
            arrSqlParameter(1).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(1).Value = objViajesBE.Serv


            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@Desde"
            arrSqlParameter(2).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(2).Value = objViajesBE.Farr


            arrSqlParameter(3) = New SqlParameter()
            arrSqlParameter(3).ParameterName = "@Hasta"
            arrSqlParameter(3).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(3).Value = objViajesBE.Fzar


            arrSqlParameter(4) = New SqlParameter()
            arrSqlParameter(4).ParameterName = "@DesNave"
            arrSqlParameter(4).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(4).Value = objViajesBE.DesNave

            ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, arrSqlParameter)
            dt = ds.Tables(0)

            If dt.Rows.Count > 0 Then

                While intI < dt.Rows.Count
                    objVjeBE = New ViajesBE()
                    objVjeBE.Viaje = dt.Rows(intI).ItemArray(0).ToString
                    objVjeBE.CodNave = dt.Rows(intI).ItemArray(1).ToString
                    objVjeBE.DesNave = dt.Rows(intI).ItemArray(2).ToString
                    objVjeBE.Dire = dt.Rows(intI).ItemArray(3).ToString
                    objVjeBE.Nvia = dt.Rows(intI).ItemArray(4).ToString
                    objVjeBE.Line = dt.Rows(intI).ItemArray(5).ToString
                    objVjeBE.Serv = dt.Rows(intI).ItemArray(6).ToString
                    objVjeBE.Farr = dt.Rows(intI).ItemArray(7).ToString
                    objVjeBE.Fzar = dt.Rows(intI).ItemArray(8).ToString
                    intI = intI + 1

                    objListaViajes.Add(objVjeBE)
                End While
            End If
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return objListaViajes
    End Function
End Class
